package org.example.service;


import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.Optional;

public class POITestService {


    private static final String FILE_PATH = "C:\\Users\\yubo\\Desktop\\test.xlsx";

    public void testPOIDeleteRow() {
        try (InputStream inputStream = new FileInputStream(FILE_PATH);
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            // Check if the file exists and is not empty
            File file = new File(FILE_PATH);
            if (!file.exists() || file.length() == 0) {
                System.err.println("File does not exist or is empty.");
                return;
            }

            Sheet sheet = workbook.getSheetAt(0);
            Row head = sheet.getRow(0);
            int toOverWriteLineStart = 0;
            int toShiftLineStart = 0;
            int toShiftLineEnd = 0;
            int lastRowNum = sheet.getLastRowNum();
            int physicalNumberOfCells = head.getPhysicalNumberOfCells();

            // 从最后一行开始循环,先删除尾巴上的值为3的行
            removeTail(sheet, physicalNumberOfCells);
            toShiftLineEnd = sheet.getLastRowNum();
            //从toShiftLineEnd开始循环
            for (int j = toShiftLineEnd; j >= 1; ) {
                //找第一个最后一个单元格值是3的行，toShiftLineStart的值是其值加1
                Row row;
                do {
                    row = sheet.getRow(j);
                    Cell lastCell = row.getCell(physicalNumberOfCells);
                    //如果是toShiftLineEnd是0，toShiftLineStart
                    if (lastCell == null || "3".equals(lastCell.getStringCellValue())) {
                        toShiftLineStart = j + 1;
                        break;
                    } else {
                        j--;
                    }
                }
                while (j >= 1);
                //如果一开始就没找到或经过最后一行也没找到，结束循环
                if (toShiftLineStart == 0 || j < 1) {
                    break;
                }
                //找到后，从找到的 toShiftLineStart行开始，找到第一个最后一个单元格值是6的行，toOverWriteLineStart是其值加1
                do {
                    row = sheet.getRow(j);
                    Cell lastCell = row.getCell(physicalNumberOfCells);
                    if (lastCell == null || "6".equals(lastCell.getStringCellValue())) {
                        toOverWriteLineStart = j + 1;
                        break;
                    } else {
                        j--;
                    }
                }
                while (j >= 1);
                //如果一开始就没找到或经过最后一行没找到，toOverWriteLineStart的值就是j的值，并移动到j，结束循环
                if (toOverWriteLineStart == 0 || j < 1) {
                    toOverWriteLineStart = j;
                }
                //找到后，将toShiftLineStart到toShiftLineEnd之间的行数据，移动toOverWriteLineStart-toShiftLineStart行
                int dist = toOverWriteLineStart - toShiftLineStart;
                sheet.shiftRows(toShiftLineStart, toShiftLineEnd, dist);
                toShiftLineEnd += dist;
                toShiftLineStart += dist;
            }
           // removeTail(sheet, physicalNumberOfCells);
            // Write changes to a new file
            try (OutputStream outputStream = new FileOutputStream(FILE_PATH)) {
                workbook.write(outputStream);
            }

            System.out.println("testPOIDelete");

        } catch (FileNotFoundException e) {
            System.err.println("File not found: " + e.getMessage());
        } catch (IOException e) {
            System.err.println("Error processing file: " + e.getMessage());
        }
    }

    private static void removeTail(Sheet sheet, int physicalNumberOfCells) {
        int i = sheet.getLastRowNum();
        do {
            Row row = sheet.getRow(i);
            Cell lastCell = Optional.ofNullable(row.getCell(physicalNumberOfCells))
                    .orElseGet(() -> {
                        Cell cell = row.createCell(physicalNumberOfCells);
                        cell.setCellValue("");
                        return cell;
                    });
            if ("6".equals(lastCell.getStringCellValue())) {
                break;
            } else {
                sheet.removeRow(row);
                i = sheet.getLastRowNum();
            }
        }
        while (i >= 1);
    }
}
